from flask import Flask, request, jsonify, render_template
from flask import send_file
import pyodbc
import datetime
import time
import hashlib
import json
import pandas as pd
import io
import sys
import os

# 资源路径处理
if getattr(sys, 'frozen', False):
    # 打包后的路径
    template_dir = os.path.join(sys._MEIPASS, 'templates')
    static_dir = os.path.join(sys._MEIPASS, 'static')
else:
    # 开发环境路径
    template_dir = 'templates'
    static_dir = 'static'

app = Flask(__name__, template_folder=template_dir, static_folder=static_dir)


# Access数据库连接配置
DB_PATH = r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=\\Jmmd-m70pez39\e\PY_DM\顺引\DATABASE.accdb;'

# 简单的内存缓存
query_cache = {}
CACHE_EXPIRY = 300  # 5分钟缓存过期时间


def clear_cache_for_order(order_no):
    """清除指定单号的所有缓存"""
    global query_cache
    keys_to_remove = []
    for cache_key in query_cache.keys():
        if order_no in cache_key:
            keys_to_remove.append(cache_key)
    
    for key in keys_to_remove:
        del query_cache[key]


def get_db_connection():
    return pyodbc.connect(DB_PATH)


def init_validation_history_table():
    """初始化验证记录历史表"""
    conn = None  # 确保conn在finally块中始终可访问
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        # 使用更安全的方法检查表是否存在
        try:
            cursor.execute("SELECT TOP 1 * FROM 验证记录历史")
            print("验证记录历史表已存在")
            return
        except Exception:
            # 表不存在，创建新表
            pass

        # 创建验证记录历史表
        cursor.execute("""
            CREATE TABLE 验证记录历史 (
                id AUTOINCREMENT PRIMARY KEY,
                [单号] TEXT,
                [物料编码] TEXT,
                [排序] INTEGER,
                [SN] TEXT,
                [唯一码] TEXT,
                [物料外码] TEXT,
                [扫描顺序] INTEGER,
                [验证结果] INTEGER,
                [扫描时间] DATETIME
            )
        """)

        # 创建索引以提高查询性能
        try:
            cursor.execute("CREATE INDEX idx_单号 ON 验证记录历史 ([单号])")
            print("索引 idx_单号 创建成功")
        except Exception as e:
            print(f"索引 idx_单号 创建失败（可能已存在）: {str(e)}")

        try:
            cursor.execute("CREATE INDEX idx_扫描时间 ON 验证记录历史 ([扫描时间])")
            print("索引 idx_扫描时间 创建成功")
        except Exception as e:
            print(f"索引 idx_扫描时间 创建失败（可能已存在）: {str(e)}")

        try:
            cursor.execute("CREATE INDEX idx_单号_扫描时间 ON 验证记录历史 ([单号], [扫描时间])")
            print("索引 idx_单号_扫描时间 创建成功")
        except Exception as e:
            print(f"索引 idx_单号_扫描时间 创建失败（可能已存在）: {str(e)}")

        conn.commit()
        print("验证记录历史表创建成功")

    except Exception as e:
        print(f"初始化验证记录历史表失败: {str(e)}")
    finally:
        if conn:  # 确保conn存在再关闭
            conn.close()


def reset_order_status(order_no):
    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        # 删除该单号的所有验证记录
        cursor.execute("DELETE FROM 验证记录 WHERE [单号] = ?", (order_no,))
        
        # 注意：历史表中的数据保留，不删除，用于数据累计和审计
        
        conn.commit()

        return True
    except Exception as e:
        print(f"重置单号状态失败: {str(e)}")
        return False
    finally:
        conn.close()


@app.route('/')
def index():
    return render_template('index.html')

@app.route('/download')
def download():
    file_path = "\H511F0A8A__20250710225805.apk"
    return send_file(
        file_path,
        as_attachment=True,
        download_name="H511F0A8A__20250710225805.apk",  # 设置下载时的文件名
        mimetype='application/vnd.android.package-archive'  # 设置正确的MIME类型
    )
@app.route('/scan_order', methods=['POST'])
def scan_order():
    order_no = request.form.get('order_no')
    reset = request.form.get('reset', 'false') == 'true'
    conn=None
    if not order_no:
        return jsonify(success=False, message="请扫描单号")

    try:
        if reset:
            if not reset_order_status(order_no):
                return jsonify(success=False, message="重置单号状态失败")

        conn = get_db_connection()
        cursor = conn.cursor()

        cursor.execute("""
            SELECT [排序], [物料编码], [物料名称]
            FROM VehicleDetails2
            WHERE [单号] = ?
            ORDER BY [排序]
        """, (order_no,))
        items = [(int(row[0]), row[1], row[2]) for row in cursor.fetchall()]

        if not items:
            return jsonify(success=False, message="单号不存在")

        cursor.execute("""
            SELECT [排序], [验证结果]
            FROM 验证记录 
            WHERE [单号] = ?
            ORDER BY [扫描时间]
        """, (order_no,))
        scanned_records = [(int(row[0]), bool(row[1])) for row in cursor.fetchall()]

        # 提取已扫描的排序号和验证状态
        scanned_orders = [record[0] for record in scanned_records]
        validation_status = {record[0]: record[1] for record in scanned_records}

        # 获取所有物料的排序号
        all_orders = sorted({item[0] for item in items})

        # 确定下一个需要扫描的排序号
        next_order = None
        for order in all_orders:
            if order not in scanned_orders:
                next_order = order
                break

        # 获取当前物料信息
        current_item = next((item for item in items if item[0] == next_order), None) if next_order else None

        # 构建物料状态列表
        material_status = []
        for item in items:
            status = "等待扫描"
            valid = None
            if item[0] in scanned_orders:
                if validation_status.get(item[0], False):
                    status = "验证成功"
                    valid = True
                else:
                    status = "验证失败"
                    valid = False
            elif item[0] == next_order:
                status = "当前待扫描"
            material_status.append({
                'sort': item[0],
                'material_code': item[1],
                'material_name': item[2],
                'status': status,
                'valid': valid
            })

        return jsonify(
            success=True,
            order_no=order_no,
            items=material_status,
            next_order=next_order,
            current_material=current_item[1] if current_item else None,
            current_material_name=current_item[2] if current_item else None,
            scanned_count=len(scanned_orders),
            total_count=len(items)
        )

    except Exception as e:
        return jsonify(success=False, message=f"数据库错误: {str(e)}")
    finally:
        if conn:
            conn.close()


@app.route('/scan_sn', methods=['POST'])
def scan_sn():
    order_no = request.form.get('order_no')
    sn = request.form.get('sn')
    current_sort = request.form.get('current_sort')
    current_material = request.form.get('current_material')
    conn=None
    if not all([order_no, sn, current_sort, current_material]):
        return jsonify(success=False, message="参数缺失")

    try:
        conn = get_db_connection()
        cursor = conn.cursor()

        sn_prefix = sn[:5] if sn else ""  # 取SN前5位作为唯一码

        # 查询物料外码
        cursor.execute("""
            SELECT [外部编码] 
            FROM 简码匹配 
            WHERE [唯一码] = ?
        """, (sn_prefix,))
        external_code_row = cursor.fetchone()
        external_code = external_code_row[0] if external_code_row else None

        # 核心验证逻辑
        is_valid = False
        validation_message = ""

        if external_code:
            if external_code == current_material:
                is_valid = True
                validation_message = "物料匹配"
            else:
                cursor.execute("""
                    SELECT [物料名称] 
                    FROM VehicleDetails2 
                    WHERE [物料编码] = ?
                """, (external_code,))
                matched_material_row = cursor.fetchone()
                matched_material_name = matched_material_row[0] if matched_material_row else "未知物料"

                cursor.execute("""
                    SELECT [物料名称] 
                    FROM VehicleDetails2 
                    WHERE [物料编码] = ? AND [单号] = ? AND [排序] = ?
                """, (current_material, order_no, current_sort))
                current_material_row = cursor.fetchone()
                current_material_name = current_material_row[0] if current_material_row else "未知物料"

                validation_message = f"物料不匹配! 应为: {current_material_name}({current_material}), 扫描到: {matched_material_name}({external_code})"
        else:
            validation_message = "未找到匹配的物料外码"

        # 记录验证结果（无论是否匹配都记录）
        scan_time = datetime.datetime.now()
        try:
            # 插入到验证记录表
            cursor.execute("""
                INSERT INTO 验证记录 (
                    [单号], [物料编码], [排序], [SN], [唯一码], 
                    [物料外码], [扫描顺序], [验证结果], [扫描时间]
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                order_no, current_material, current_sort, sn, sn_prefix,
                external_code, current_sort, 1 if is_valid else 0, scan_time
            ))
            
            # 同时插入到验证记录历史表
            cursor.execute("""
                INSERT INTO 验证记录历史 (
                    [单号], [物料编码], [排序], [SN], [唯一码], 
                    [物料外码], [扫描顺序], [验证结果], [扫描时间]
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
            """, (
                order_no, current_material, current_sort, sn, sn_prefix,
                external_code, current_sort, 1 if is_valid else 0, scan_time
            ))
            
            conn.commit()
            
            # 清除相关缓存
            clear_cache_for_order(order_no)
            
        except Exception as e:
            return jsonify(success=False, message=f"验证记录保存失败: {str(e)}")

        # 获取所有排序号
        cursor.execute("""
            SELECT DISTINCT [排序] 
            FROM VehicleDetails2 
            WHERE [单号] = ? 
            ORDER BY [排序]
        """, (order_no,))
        all_orders = sorted({int(row[0]) for row in cursor.fetchall()})

        # 获取已扫描的排序号
        cursor.execute("""
            SELECT DISTINCT [排序] 
            FROM 验证记录 
            WHERE [单号] = ?
        """, (order_no,))
        scanned_orders = {int(row[0]) for row in cursor.fetchall()}

        # 找到下一个未扫描的排序号
        next_order = None
        for order in all_orders:
            if order not in scanned_orders:
                next_order = order
                break

        # 获取下一个物料信息
        next_material_code = None
        next_material_name = None
        if next_order is not None:
            cursor.execute("""
                SELECT [物料编码], [物料名称]
                FROM VehicleDetails2 
                WHERE [单号] = ? AND [排序] = ?
            """, (order_no, int(next_order)))
            next_material = cursor.fetchone()
            if next_material:
                next_material_code = next_material[0]
                next_material_name = next_material[1]

        return jsonify(
            success=True,
            is_valid=is_valid,
            next_order=next_order,
            next_material_code=next_material_code,
            next_material_name=next_material_name,
            message=validation_message,
            scanned_count=len(scanned_orders),
            total_count=len(all_orders),
            current_sort=int(current_sort) if current_sort else 0
        )

    except Exception as e:
        return jsonify(success=False, message=f"数据库错误: {str(e)}")
    finally:
        if conn:
            conn.close()


@app.route('/dhcx')
def history_query():
    """历史记录查询页面"""
    return render_template('history_query.html')


@app.route('/test')
def test_page():
    """测试页面"""
    return render_template('test.html')


@app.route('/api/history', methods=['POST'])
def get_history_records():
    """获取历史记录API"""
    order_no = request.form.get('order_no')
    page = int(request.form.get('page', 1))
    page_size = int(request.form.get('page_size', 50))  # 每页50条记录
    conn = None
    print(f"收到历史查询请求: 单号={order_no}, 页码={page}, 页大小={page_size}")
    
    if not order_no:
        return jsonify(success=False, message="请输入单号")
    
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 首先检查表是否存在
        try:
            cursor.execute("SELECT TOP 1 * FROM 验证记录历史")
            print("验证记录历史表存在")
        except Exception as e:
            print(f"验证记录历史表不存在或无法访问: {str(e)}")
            return jsonify(success=False, message="历史记录表不存在，请先进行一些扫描操作")
        
        # 简化查询：先获取所有记录
        print(f"开始查询单号 {order_no} 的历史记录...")
        cursor.execute("""
            SELECT [单号], [物料编码], [排序], [SN], [唯一码], 
                   [物料外码], [扫描顺序], [验证结果], [扫描时间]
            FROM 验证记录历史 
            WHERE [单号] = ?
            ORDER BY [扫描时间] ASC
        """, (order_no,))
        
        all_records = cursor.fetchall()
        print(f"查询到 {len(all_records)} 条记录")
        
        if len(all_records) == 0:
            return jsonify(
                success=True,
                records=[],
                statistics={'total': 0, 'success': 0, 'fail': 0, 'success_rate': 0},
                pagination={'current_page': 1, 'total_pages': 0, 'total_records': 0}
            )
        
        # 手动实现分页
        total_count = len(all_records)
        total_pages = (total_count + page_size - 1) // page_size
        offset = (page - 1) * page_size
        start_index = offset
        end_index = min(start_index + page_size, len(all_records))
        records_data = all_records[start_index:end_index]
        
        print(f"分页信息: 总数={total_count}, 总页数={total_pages}, 当前页={page}, 偏移={offset}")
        
        records = []
        success_count = 0
        fail_count = 0
        
        for row in records_data:
            validation_result = '成功' if int(row[7]) == 1 else '失败'
            if validation_result == '成功':
                success_count += 1
            else:
                fail_count += 1
            records.append({
                'order_no': row[0],
                'material_code': row[1],
                'sort': row[2],
                'sn': row[3],
                'unique_code': row[4],
                'external_code': row[5] if row[5] else '',
                'scan_order': row[6],
                'validation_result': validation_result,
                'scan_time': row[8].strftime('%Y-%m-%d %H:%M:%S') if row[8] else ''
            })
        
        response_data = {
            'success': True,
            'records': records,
            'statistics': {
                'total': total_count,
                'success': success_count,
                'fail': fail_count,
                'success_rate': round(success_count / total_count * 100, 2) if total_count > 0 else 0
            },
            'pagination': {
                'current_page': page,
                'total_pages': total_pages,
                'total_records': total_count,
                'page_size': page_size
            }
        }
        
        print(f"查询完成: 返回 {len(records)} 条记录")
        return jsonify(response_data)
        
    except Exception as e:
        print(f"查询失败: {str(e)}")
        return jsonify(success=False, message=f"查询失败: {str(e)}")
    finally:
        if conn:
            conn.close()


# 导出历史记录为Excel
@app.route('/api/history/export', methods=['GET'])
def export_history_excel():
    order_no = request.args.get('order_no')
    if not order_no:
        return jsonify(success=False, message="缺少单号参数"), 400
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        cursor.execute("""
            SELECT [单号], [物料编码], [排序], [SN], [唯一码], [物料外码], [扫描顺序], [验证结果], [扫描时间]
            FROM 验证记录历史
            WHERE [单号] = ?
            ORDER BY [扫描时间] ASC
        """, (order_no,))
        rows = cursor.fetchall()
        if not rows:
            return jsonify(success=False, message="无历史记录可导出"), 404
        # 构建DataFrame
        data = []
        for row in rows:
            data.append({
                '单号': row[0],
                '物料编码': row[1],
                '排序': row[2],
                'SN': row[3],
                '唯一码': row[4],
                '物料外码': row[5],
                '扫描顺序': row[6],
                '验证结果': '成功' if int(row[7]) == 1 else '失败',
                '扫描时间': row[8].strftime('%Y-%m-%d %H:%M:%S') if row[8] else ''
            })
        df = pd.DataFrame(data)
        output = io.BytesIO()
        # 兼容pandas和openpyxl写入BytesIO的标准做法
        with pd.ExcelWriter(output, engine='openpyxl') as writer:
            df.to_excel(writer, index=False, sheet_name='历史记录')
        output.seek(0)
        filename = f"历史记录_{order_no}.xlsx"
        return send_file(output, as_attachment=True, download_name=filename, mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    except Exception as e:
        return jsonify(success=False, message=f"导出失败: {str(e)}"), 500
    finally:
        conn.close()


@app.route('/test_db')
def test_database():
    """测试数据库连接和表结构"""
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        # 测试基本连接
        cursor.execute("SELECT 1")
        result = cursor.fetchone()
        print("数据库连接正常")
        
        # 检查验证记录历史表
        try:
            cursor.execute("SELECT COUNT(*) FROM 验证记录历史")
            count_result = cursor.fetchone()
            count = count_result[0] if count_result else 0
            print(f"验证记录历史表存在，共有 {count} 条记录")
            
            # 获取一些示例数据
            cursor.execute("SELECT TOP 5 * FROM 验证记录历史 ORDER BY [扫描时间] DESC")
            sample_records = cursor.fetchall()
            print(f"示例记录: {len(sample_records)} 条")
            
            return jsonify({
                'success': True,
                'message': f'数据库连接正常，验证记录历史表存在，共有 {count} 条记录',
                'sample_count': len(sample_records)
            })
            
        except Exception as e:
            print(f"验证记录历史表访问失败: {str(e)}")
            return jsonify({
                'success': False,
                'message': f'验证记录历史表访问失败: {str(e)}'
            })
            
    except Exception as e:
        print(f"数据库连接失败: {str(e)}")
        return jsonify({
            'success': False,
            'message': f'数据库连接失败: {str(e)}'
        })
    finally:
        conn.close()


if __name__ == '__main__':
    # 初始化验证记录历史表
    init_validation_history_table()
    app.run(host='0.0.0.0', port=5007, debug=True)